package cn.q3c.stock.task;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.log4j.Logger;
import org.xBaseJ.xBaseJException;

import cn.q3c.stock.data.DBFUtils;
import cn.q3c.stock.object.LogCJ;
import cn.q3c.stock.object.Stock;

public class LogToDB {

	private static PropertiesConfiguration configuration = null;
		
	private static Logger logger = Logger.getLogger(LogToDB.class);
	
	private static Connection getConnection() throws SQLException, ClassNotFoundException
	{
		Class.forName(configuration.getString("db.driver"));
        Connection conn = DriverManager.getConnection(
        		configuration.getString("db.connUrl"),
        		configuration.getString("db.username"),
        		configuration.getString("db.password"));
        return conn;
	}
	
	public static List<Stock> getStockList() throws xBaseJException, IOException, ConfigurationException
	{
		
		configuration = new PropertiesConfiguration(
				"stock.properties");
		
		List<Stock> shStockList = DBFUtils.toStockList(new File(configuration
				.getString("file.dbf.sh")));
		List<Stock> szStockList = DBFUtils.toStockList(new File(configuration
				.getString("file.dbf.sz")));
		List<Stock> stockList = new ArrayList<Stock>();
		stockList.addAll(shStockList);
		stockList.addAll(szStockList);
		
		return stockList;
	}
	
	/**
	 * 初始化某一天的数据，先删除，再添加
	 * @param date
	 * @throws SQLException 
	 * @throws ParseException 
	 * @throws IOException 
	 * @throws xBaseJException 
	 * @throws ClassNotFoundException 
	 */
	public static void initCJLogByDate( Date date, Stock stock ) throws SQLException, xBaseJException, IOException, ParseException, ClassNotFoundException
	{
		String scFlag = stock.getSctype() == Stock.SC_SHANGHAI ? "sh"
				: "sz";

		if ((stock.getSctype() == Stock.SC_SHANGHAI && StringUtils
				.startsWith(stock.getCode(), "60"))
				|| (stock.getSctype() == Stock.SC_SHENZHEN && (StringUtils
						.startsWith(stock.getCode(), "00") || StringUtils
						.startsWith(stock.getCode(), "30"))))
		{
			String year = DateFormatUtils.format(date, "yyyy");
			String month = DateFormatUtils.format(date, "MM");
			String day = DateFormatUtils.format(date, "dd");
			String dateStr = year + "-" + month + "-" + day;
			
			String fileName2 = scFlag + stock.getCode()
					+ "tdetail_" + dateStr + ".xls";

			String savePath = configuration.getString("file.path");// 下载文件本地保存的根目录
			File file2 = new File(savePath + year
					+ File.separator + month + File.separator
					+ day + File.separator + fileName2);
			
			if( !file2.exists() || FileUtils.sizeOf(file2) == 0 )
			{
				//logger.debug("文件不存在或者内容为空" + file2);
				return;
			}			
			
			List<LogCJ> logList = DBFUtils.toLogCJList(file2);
			
			QueryRunner qr = new QueryRunner();
			String sql = "insert into log_cj2(`code`,`sctype`,`price`,`count`,`priceCount`,`mmp`,`cdate`) " +
					"values(?,?,?,?,?,?,?)";
			
			Object[][] batchobjs = null;
			for( LogCJ log:logList )
			{
				
				Object[] objs = new Object[] { 
						log.getCode(),
						log.getSctype(),
						log.getPrice(),
						log.getCount(),
						log.getPriceCount(),
						log.getMmp(),
						log.getCdate()};
				batchobjs = (Object[][])ArrayUtils.add(batchobjs, objs);
				
			}
			
			Connection conn = getConnection();
			qr.batch(conn, sql, batchobjs);
			DbUtils.close(conn);

		}
	}
	
	public static Date strToDate( String dateStr ) throws ParseException
	{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		if( StringUtils.equals(dateStr, "now()") )
		{ 
			return sdf.parse( sdf.format(new Date()) );
		}
		else
		{
			return sdf.parse(dateStr);
		}
	}
	
	//2013行情入库
	public static void initCJLog() throws ParseException//SQLException, ClassNotFoundException, xBaseJException, IOException, ParseException
, xBaseJException, IOException, ConfigurationException
	{
		
		String importdate = configuration.getString("import.date");
		String startDateStr = StringUtils.substringBefore(importdate, "~");
		String endDateStr = StringUtils.substringAfter(importdate, "~");
		
		Date sDate = strToDate(startDateStr);
		Date eDate = strToDate(endDateStr);
		
		//1.导入股票词典表
		List<Stock> stockList = getStockList();
				
		Calendar calendar = Calendar.getInstance();//2013
		calendar.setTime(sDate);
		
		while( calendar.getTimeInMillis() >= eDate.getTime()  )
		{
			int dayofweek = calendar.get(Calendar.DAY_OF_WEEK);
			// 周日 周六 跳过
			if (dayofweek == 1 || dayofweek == 7) {
				calendar.add(Calendar.DATE, -1);// 向前
				continue;
			}
			
			logger.debug("进入" + DateFormatUtils.format(calendar.getTimeInMillis(), "yyyy-MM-dd"));
			
			for (Stock stock : stockList) {
				
				try
				{
					initCJLogByDate(calendar.getTime(), stock);
				}
				catch( Exception e )
				{
					logger.debug("股票成交入库异常：" + stock.getSctype() + "_" + stock.getCode(), e);
				}
			}
			
			calendar.add(Calendar.DATE, -1);
		}
	}
	
	/**
	 * @param args
	 * @throws ConfigurationException 
	 * @throws IOException 
	 * @throws xBaseJException 
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @throws ParseException 
	 */
	public static void main(String[] args) throws ConfigurationException, xBaseJException, IOException, ClassNotFoundException, SQLException, ParseException {

		configuration = new PropertiesConfiguration(
				"stock.properties");
		
		createStockDict();
		initCJLog();
		
	}
	
	
	/**
	 * 建立词典表，首先清空，然后写入
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws xBaseJException
	 * @throws IOException
	 * @throws ConfigurationException 
	 */
	public static void createStockDict() throws SQLException, ClassNotFoundException, xBaseJException, IOException, ConfigurationException
	{
		//0.建立连接
        Connection conn = getConnection();
		
		//1.导入股票词典表
		List<Stock> stockList = getStockList();
		QueryRunner qr = new QueryRunner();	
		
		//清空表
		String sql = "TRUNCATE TABLE `stock_dict`";
		qr.update(conn, sql);
		
		// 上海股票
		for (Stock stock : stockList) {

			if ((stock.getSctype() == Stock.SC_SHANGHAI && StringUtils
					.startsWith(stock.getCode(), "60"))
					|| (stock.getSctype() == Stock.SC_SHENZHEN && (StringUtils
							.startsWith(stock.getCode(), "00") || StringUtils
							.startsWith(stock.getCode(), "30")))) {

				//入库
				sql = "insert into stock_dict(code,sctype,name) values(?,?,?)";
				Object[] objs = new Object[] { stock.getCode(),
						stock.getSctype(),StringUtils.deleteWhitespace( stock.getName() ) };
				
				qr.update(conn, sql, objs);
				
			}
		}
		
		DbUtils.close(conn);
	}
}